﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Utility;
namespace Service
{
    public  class AgeReportService
    {
         public DataSet GetReport(string tjnd, string schooltype, string districtid)
        {
            if (string.IsNullOrEmpty(schooltype))
            {
                if (string.IsNullOrEmpty(districtid))
                {
                    string sql = @"SELECT a.Age as Tjnd,a.Age as gradebh,a.xb,sum(a.sj) as sj,sum(a.h1) as h1,sum(a.h2) as h2,sum(a.h3) as h3,sum(a.h4) as h4,sum(a.h5) as h5,sum(a.h6) as h6,sum(a.h7) as h7,sum(a.h8) as h8,sum(a.h9) as h9,sum(a.h10) as h10,sum(a.h11) as h11  from Report_ZB_Age a  
                    where tjnd=@tjnd and rid='01' group by a.Age,a.xb order by a.Age,xb";
                    SqlParameter[] ps = new SqlParameter[]{
                    new SqlParameter("@tjnd",tjnd),
                    };
                    DataSet ds = SqlHelper.ExecuteDataset(WebConn.connString, CommandType.Text, sql, ps);
                    return ds;
                }
                else
                {
                    string sql = @"SELECT a.Age as Tjnd,a.Age as gradebh,a.xb,sum(a.sj) as sj,sum(a.h1) as h1,sum(a.h2) as h2,sum(a.h3) as h3,sum(a.h4) as h4,sum(a.h5) as h5,sum(a.h6) as h6,sum(a.h7) as h7,sum(a.h8) as h8,sum(a.h9) as h9,sum(a.h10) as h10,sum(a.h11) as h11  from Report_ZB_Age a   
                   where  tjnd=@tjnd and rid='01' and (a.DISTRICTID=@districtid or a.cityid=@districtid)  group by a.Age,a.xb order by a.Age,xb";
                    SqlParameter[] ps = new SqlParameter[]{
                    new SqlParameter("@tjnd",tjnd),
                    new SqlParameter("@districtid",districtid)
                    };
                    DataSet ds = SqlHelper.ExecuteDataset(WebConn.connString, CommandType.Text, sql, ps);
                    return ds;
                }
            }
            else
            {
                if (string.IsNullOrEmpty(districtid))
                {
                    string sql = @"SELECT a.Age as Tjnd,a.Age as gradebh,a.xb,sum(a.sj) as sj,sum(a.h1) as h1,sum(a.h2) as h2,sum(a.h3) as h3,sum(a.h4) as h4,sum(a.h5) as h5,sum(a.h6) as h6,sum(a.h7) as h7,sum(a.h8) as h8,sum(a.h9) as h9,sum(a.h10) as h10,sum(a.h11) as h11  from Report_ZB_Age a   
                where schooltype=@schooltype and tjnd=@tjnd and rid='01' group by a.Age,a.xb order by a.Age,xb";
                    SqlParameter[] ps = new SqlParameter[]{
                new SqlParameter("@schooltype",schooltype),
                new SqlParameter("@tjnd",tjnd),
            };
                    DataSet ds = SqlHelper.ExecuteDataset(WebConn.connString, CommandType.Text, sql, ps);
                    return ds;
                }
                else
                {
                    string sql = @"SELECT a.Age as Tjnd,a.Age as gradebh,a.xb,sum(a.sj) as sj,sum(a.h1) as h1,sum(a.h2) as h2,sum(a.h3) as h3,sum(a.h4) as h4,sum(a.h5) as h5,sum(a.h6) as h6,sum(a.h7) as h7,sum(a.h8) as h8,sum(a.h9) as h9,sum(a.h10) as h10,sum(a.h11) as h11  from Report_ZB_Age a     
             where schooltype=@schooltype and tjnd=@tjnd and rid='01' and (a.DISTRICTID=@districtid or a.cityid=@districtid)  group by a.Age,a.xb order by a.Age,xb";
                    SqlParameter[] ps = new SqlParameter[]{
                new SqlParameter("@schooltype",schooltype),
                new SqlParameter("@tjnd",tjnd),
                new SqlParameter("@districtid",districtid)
            };
                    DataSet ds = SqlHelper.ExecuteDataset(WebConn.connString, CommandType.Text, sql, ps);
                    return ds;
                }
            }
        }
        public DataSet GetReport2(string tjnd, string schooltype, string districtid)
        {
            if (string.IsNullOrEmpty(schooltype))
            {
                if (string.IsNullOrEmpty(districtid))
                {
                    string sql = @"
                    SELECT a.Age as Tjnd,a.Age as gradebh,a.xb,sum(a.sj) as sj,sum(a.h1) as h1,sum(a.h2) as h2,sum(a.h3) as h3,sum(a.h4) as h4,sum(a.h5) as h5,sum(a.h6) as h6,sum(a.h7) as h7,sum(a.h8) as h8,sum(a.h9) as h9,sum(a.h10) as h10,sum(a.h11) as h11,sum(isnull(a.h4,0)+isnull(a.h5,0)+isnull(a.h6,0)) as h15,sum(isnull(a.h8,0)+isnull(a.h9,0)+isnull(a.h10,0)) as h16  from Report_ZB_Age a  where
                     tjnd=@tjnd and rid='02'  group by a.Age,a.xb order by a.Age,xb    
                    ";
                    SqlParameter[] ps = new SqlParameter[]{
                    new SqlParameter("@tjnd",tjnd),
                    };
                    DataSet ds = SqlHelper.ExecuteDataset(WebConn.connString, CommandType.Text, sql, ps);
                    return ds;
                }
                else
                {
                    string sql = @"
                    SELECT a.Age as Tjnd,a.Age as gradebh,a.xb,sum(a.sj) as sj,sum(a.h1) as h1,sum(a.h2) as h2,sum(a.h3) as h3,sum(a.h4) as h4,sum(a.h5) as h5,sum(a.h6) as h6,sum(a.h7) as h7,sum(a.h8) as h8,sum(a.h9) as h9,sum(a.h10) as h10,sum(a.h11) as h11,sum(isnull(a.h4,0)+isnull(a.h5,0)+isnull(a.h6,0)) as h15,sum(isnull(a.h8,0)+isnull(a.h9,0)+isnull(a.h10,0)) as h16  from Report_ZB_Age a  where
                     tjnd=@tjnd and rid='02' and (a.DISTRICTID=@districtid or a.cityid=@districtid)  group by a.Age,a.xb order by a.Age,xb    
                    ";
                    SqlParameter[] ps = new SqlParameter[]{
                    new SqlParameter("@tjnd",tjnd),
                    new SqlParameter("@districtid",districtid)
                    };
                    DataSet ds = SqlHelper.ExecuteDataset(WebConn.connString, CommandType.Text, sql, ps);
                    return ds;
                }
            }
            else
            {
                if (string.IsNullOrEmpty(districtid))
                {
                    string sql = @"
                    SELECT a.Age as Tjnd,a.Age as gradebh,a.xb,sum(a.sj) as sj,sum(a.h1) as h1,sum(a.h2) as h2,sum(a.h3) as h3,sum(a.h4) as h4,sum(a.h5) as h5,sum(a.h6) as h6,sum(a.h7) as h7,sum(a.h8) as h8,sum(a.h9) as h9,sum(a.h10) as h10,sum(a.h11) as h11,sum(isnull(a.h4,0)+isnull(a.h5,0)+isnull(a.h6,0)) as h15,sum(isnull(a.h8,0)+isnull(a.h9,0)+isnull(a.h10,0)) as h16  from Report_ZB_Age a  where a.schooltype
                    like @schooltype and tjnd=@tjnd and rid='02'  group by a.Age,a.xb order by a.Age,xb    
                    ";
                    SqlParameter[] ps = new SqlParameter[]{
                new SqlParameter("@schooltype",schooltype),
                new SqlParameter("@tjnd",tjnd),
                };
                    DataSet ds = SqlHelper.ExecuteDataset(WebConn.connString, CommandType.Text, sql, ps);
                    return ds;
                }
                else
                {
                    string sql = @"
                    SELECT a.Age as Tjnd,a.Age as gradebh,a.xb,sum(a.sj) as sj,sum(a.h1) as h1,sum(a.h2) as h2,sum(a.h3) as h3,sum(a.h4) as h4,sum(a.h5) as h5,sum(a.h6) as h6,sum(a.h7) as h7,sum(a.h8) as h8,sum(a.h9) as h9,sum(a.h10) as h10,sum(a.h11) as h11,sum(isnull(a.h4,0)+isnull(a.h5,0)+isnull(a.h6,0)) as h15,sum(isnull(a.h8,0)+isnull(a.h9,0)+isnull(a.h10,0)) as h16  from Report_ZB_Age a  where a.schooltype
                    like @schooltype and tjnd=@tjnd and rid='02' and (a.DISTRICTID=@districtid or a.cityid=@districtid)  group by a.Age,a.xb order by a.Age,xb    
                    ";
                    SqlParameter[] ps = new SqlParameter[]{
                new SqlParameter("@schooltype",schooltype),
                new SqlParameter("@tjnd",tjnd),
                new SqlParameter("@districtid",districtid)
            };
                    DataSet ds = SqlHelper.ExecuteDataset(WebConn.connString, CommandType.Text, sql, ps);
                    return ds;
                }
            }
        }
        public DataSet GetReport3(string tjnd, string schooltype, string districtid)
        {
            if (string.IsNullOrEmpty(schooltype))
            {
                if (string.IsNullOrEmpty(districtid))
                {
                    string sql = @"
                    SELECT a.Age as Tjnd,a.Age as gradebh,a.xb,sum(a.sj) as sj,sum(a.h1) as h1,sum(a.h2) as h2,sum(a.h3) as h3,sum(a.h4) as h4,sum(a.h5) as h5,sum(a.h6) as h6,sum(a.h7) as h7,sum(a.h8) as h8,sum(a.h9) as h9,sum(a.h10) as h10 from Report_ZB_Age a  where 
                    tjnd=@tjnd and rid='03' group by a.Age,a.xb order by a.Age,xb  
                    ";
                    SqlParameter[] ps = new SqlParameter[]{
                    new SqlParameter("@tjnd",tjnd),
                    };
                    DataSet ds = SqlHelper.ExecuteDataset(WebConn.connString, CommandType.Text, sql, ps);
                    return ds;
                }
                else
                {
                    string sql = @"
                    SELECT a.Age as Tjnd,a.Age as gradebh,a.xb,sum(a.sj) as sj,sum(a.h1) as h1,sum(a.h2) as h2,sum(a.h3) as h3,sum(a.h4) as h4,sum(a.h5) as h5,sum(a.h6) as h6,sum(a.h7) as h7,sum(a.h8) as h8,sum(a.h9) as h9,sum(a.h10) as h10 from Report_ZB_Age a  where
                     tjnd=@tjnd and rid='03' and (a.DISTRICTID=@districtid or a.cityid=@districtid) group by a.Age,a.xb order by a.Age,xb  
                    ";
                    SqlParameter[] ps = new SqlParameter[]{
                    new SqlParameter("@tjnd",tjnd),
                    new SqlParameter("@districtid",districtid),
                    };
                    DataSet ds = SqlHelper.ExecuteDataset(WebConn.connString, CommandType.Text, sql, ps);
                    return ds;
                }
            }
            else
            {
                if (string.IsNullOrEmpty(districtid))
                {
                    string sql = @"
                    SELECT a.Age as Tjnd,a.Age as gradebh,a.xb,sum(a.sj) as sj,sum(a.h1) as h1,sum(a.h2) as h2,sum(a.h3) as h3,sum(a.h4) as h4,sum(a.h5) as h5,sum(a.h6) as h6,sum(a.h7) as h7,sum(a.h8) as h8,sum(a.h9) as h9,sum(a.h10) as h10 from Report_ZB_Age a  where schooltype 
                    like @schooltype and tjnd=@tjnd and rid='03' group by a.Age,a.xb order by a.Age,xb  
                    ";
                    SqlParameter[] ps = new SqlParameter[]{
                    new SqlParameter("@schooltype",schooltype),
                    new SqlParameter("@tjnd",tjnd),
                    };
                    DataSet ds = SqlHelper.ExecuteDataset(WebConn.connString, CommandType.Text, sql, ps);
                    return ds;
                }
                else
                {
                    string sql = @"
                    SELECT a.Age as Tjnd,a.Age as gradebh,a.xb,sum(a.sj) as sj,sum(a.h1) as h1,sum(a.h2) as h2,sum(a.h3) as h3,sum(a.h4) as h4,sum(a.h5) as h5,sum(a.h6) as h6,sum(a.h7) as h7,sum(a.h8) as h8,sum(a.h9) as h9,sum(a.h10) as h10 from Report_ZB_Age a  where schooltype 
                    like @schooltype and tjnd=@tjnd and rid='03' and (a.DISTRICTID=@districtid or a.cityid=@districtid) group by a.Age,a.xb order by a.Age,xb  
                    ";
                    SqlParameter[] ps = new SqlParameter[]{
                    new SqlParameter("@schooltype",schooltype),
                    new SqlParameter("@tjnd",tjnd),
                    new SqlParameter("@districtid",districtid),
                    };
                    DataSet ds = SqlHelper.ExecuteDataset(WebConn.connString, CommandType.Text, sql, ps);
                    return ds;
                }
            }

        }
        public DataSet GetReport4(string tjnd, string schooltype, string districtid)
        {
            if (string.IsNullOrEmpty(schooltype))
            {
                if (string.IsNullOrEmpty(districtid))
                {
                    string sql = @"
                    SELECT a.Age as Tjnd,a.Age as gradebh,a.xb,sum(a.sj) as sj,sum(a.h1) as h1,sum(a.h2) as h2,sum(a.h3) as h3,sum(a.h4) as h4,sum(a.h5) as h5,sum(a.h6) as h6,sum(a.h7) as h7,sum(a.h8) as h8,sum(a.h9) as h9,sum(a.h10) as h10 from Report_ZB_Age a  where 
                     tjnd=@tjnd and rid='04' group by a.Age,a.xb order by a.Age,xb
                    ";
                    SqlParameter[] ps = new SqlParameter[]{
                    new SqlParameter("@tjnd",tjnd),
                    };
                    DataSet ds = SqlHelper.ExecuteDataset(WebConn.connString, CommandType.Text, sql, ps);
                    return ds;
                }
                else
                {
                    string sql = @"
                    SELECT a.Age as Tjnd,a.Age as gradebh,a.xb,sum(a.sj) as sj,sum(a.h1) as h1,sum(a.h2) as h2,sum(a.h3) as h3,sum(a.h4) as h4,sum(a.h5) as h5,sum(a.h6) as h6,sum(a.h7) as h7,sum(a.h8) as h8,sum(a.h9) as h9,sum(a.h10) as h10 from Report_ZB_Age a  where 
                    tjnd=@tjnd and rid='04' and (a.DISTRICTID=@districtid or a.cityid=@districtid) group by a.Age,a.xb order by a.Age,xb
                    ";
                    SqlParameter[] ps = new SqlParameter[]{
                    new SqlParameter("@tjnd",tjnd),
                    new SqlParameter("@districtid",districtid),
                    };
                    DataSet ds = SqlHelper.ExecuteDataset(WebConn.connString, CommandType.Text, sql, ps);
                    return ds;
                }
            }
            else
            {
                if (string.IsNullOrEmpty(districtid))
                {
                    string sql = @"
                    SELECT a.Age as Tjnd,a.Age as gradebh,a.xb,sum(a.sj) as sj,sum(a.h1) as h1,sum(a.h2) as h2,sum(a.h3) as h3,sum(a.h4) as h4,sum(a.h5) as h5,sum(a.h6) as h6,sum(a.h7) as h7,sum(a.h8) as h8,sum(a.h9) as h9,sum(a.h10) as h10 from Report_ZB_Age a  where 
                    schooltype=@schooltype and tjnd=@tjnd and rid='04' group by a.Age,a.xb order by a.Age,xb
                    ";
                    SqlParameter[] ps = new SqlParameter[]{
                new SqlParameter("@schooltype",schooltype),
                new SqlParameter("@tjnd",tjnd),
            };
                    DataSet ds = SqlHelper.ExecuteDataset(WebConn.connString, CommandType.Text, sql, ps);
                    return ds;
                }
                else
                {
                    string sql = @"
                    SELECT a.Age as Tjnd,a.Age as gradebh,a.xb,sum(a.sj) as sj,sum(a.h1) as h1,sum(a.h2) as h2,sum(a.h3) as h3,sum(a.h4) as h4,sum(a.h5) as h5,sum(a.h6) as h6,sum(a.h7) as h7,sum(a.h8) as h8,sum(a.h9) as h9,sum(a.h10) as h10 from Report_ZB_Age a  where 
                    schooltype=@schooltype and tjnd=@tjnd and rid='04' and (a.DISTRICTID=@districtid or a.cityid=@districtid) group by a.Age,a.xb order by a.Age,xb
                    ";
                    SqlParameter[] ps = new SqlParameter[]{
                new SqlParameter("@schooltype",schooltype),
                new SqlParameter("@tjnd",tjnd),
                new SqlParameter("@districtid",districtid),
            };
                    DataSet ds = SqlHelper.ExecuteDataset(WebConn.connString, CommandType.Text, sql, ps);
                    return ds;
                }
            }
        }
        public DataSet GetReportSg(string tjnd, string schooltype, string districtid)
        {
            if (string.IsNullOrEmpty(schooltype))
            {
                if (string.IsNullOrEmpty(districtid))
                {
                    string sql = @"
                   SELECT a.Age as Tjnd,a.Age as gradebh,a.xb,sum(a.h1) as h1,sum(a.h2) as h2,sum(a.h3) as h3,sum(a.h4) as h4,sum(a.h5) as h5,sum(a.h6) as h6,sum(h15b) as h15b,sum(h16b) as h16b from 
                    Report_TB_Age a where tjnd=@tjnd and rid='01'  
                    group by a.Age,a.xb order by a.Age,xb
                    ";
                    SqlParameter[] ps = new SqlParameter[]{
                    new SqlParameter("@tjnd",tjnd),
                    };
                    DataSet ds = SqlHelper.ExecuteDataset(WebConn.connString, CommandType.Text, sql, ps);
                    return ds;
                }
                else
                {
                    string sql = @"
                   SELECT a.Age as Tjnd,a.Age as gradebh,a.xb,sum(a.h1) as h1,sum(a.h2) as h2,sum(a.h3) as h3,sum(a.h4) as h4,sum(a.h5) as h5,sum(a.h6) as h6,sum(h15b) as h15b,sum(h16b) as h16b from 
                    Report_TB_Age a where tjnd=@tjnd and rid='01' and (a.DISTRICTID=@districtid or a.cityid=@districtid)
                    group by a.Age,a.xb order by a.Age,xb
                    ";
                    SqlParameter[] ps = new SqlParameter[]{
                new SqlParameter("@tjnd",tjnd),
                new SqlParameter("@districtid",districtid),
                };
                    DataSet ds = SqlHelper.ExecuteDataset(WebConn.connString, CommandType.Text, sql, ps);
                    return ds;
                }
            }
            else
            {
                if (string.IsNullOrEmpty(districtid))
                {
                    string sql = @"
                   SELECT a.Age as Tjnd,a.Age as gradebh,a.xb,sum(a.h1) as h1,sum(a.h2) as h2,sum(a.h3) as h3,sum(a.h4) as h4,sum(a.h5) as h5,sum(a.h6) as h6,sum(h15b) as h15b,sum(h16b) as h16b from 
                    Report_TB_Age a where schooltype=@schooltype and tjnd=@tjnd and rid='01'  
                    group by a.Age,a.xb order by a.Age,xb
                    ";
                    SqlParameter[] ps = new SqlParameter[]{
                    new SqlParameter("@schooltype",schooltype),
                    new SqlParameter("@tjnd",tjnd),
                    };
                    DataSet ds = SqlHelper.ExecuteDataset(WebConn.connString, CommandType.Text, sql, ps);
                    return ds;
                }
                else
                {
                    string sql = @"
                   SELECT a.Age as Tjnd,a.Age as gradebh,a.xb,sum(a.h1) as h1,sum(a.h2) as h2,sum(a.h3) as h3,sum(a.h4) as h4,sum(a.h5) as h5,sum(a.h6) as h6,sum(h15b) as h15b,sum(h16b) as h16b from 
                    Report_TB_Age a where schooltype=@schooltype and tjnd=@tjnd and rid='01' and (a.DISTRICTID=@districtid or a.cityid=@districtid)
                    group by a.Age,a.xb order by a.Age,xb
                    ";
                    SqlParameter[] ps = new SqlParameter[]{
                new SqlParameter("@schooltype",schooltype),
                new SqlParameter("@tjnd",tjnd),
                new SqlParameter("@districtid",districtid),
                };
                    DataSet ds = SqlHelper.ExecuteDataset(WebConn.connString, CommandType.Text, sql, ps);
                    return ds;
                }
            }
        }
        public DataSet GetReportTz(string tjnd, string schooltype, string districtid)
        {
            if (string.IsNullOrEmpty(schooltype))
            {
                if (string.IsNullOrEmpty(districtid))
                {
                    string sql = @"
                   SELECT a.Age as Tjnd,a.Age as gradebh,a.xb,sum(a.h7) as h7,sum(a.h8) as h8,sum(a.h9) as h9,sum(a.h10) as h10,sum(a.h11) as h11,sum(a.h12) as h12,sum(a.h17b) as h17b,sum(a.h18b) as h18b
                    from Report_TB_Age a
                    where tjnd=@tjnd and rid='01'  group by a.Age,a.xb order by a.Age,xb
                    ";
                    SqlParameter[] ps = new SqlParameter[]{
                    new SqlParameter("@tjnd",tjnd),
                    };
                    DataSet ds = SqlHelper.ExecuteDataset(WebConn.connString, CommandType.Text, sql, ps);
                    return ds;
                }
                else
                {
                    string sql = @"
                    SELECT a.Age as Tjnd,a.Age as gradebh,a.xb,sum(a.h7) as h7,sum(a.h8) as h8,sum(a.h9) as h9,sum(a.h10) as h10,sum(a.h11) as h11,sum(a.h12) as h12,sum(a.h17b) as h17b,sum(a.h18b) as h18b from Report_TB_Age a
                    where tjnd=@tjnd and rid='01' and (a.DISTRICTID=@districtid or a.cityid=@districtid) group by a.Age,a.xb order by a.Age,xb
                    ";
                    SqlParameter[] ps = new SqlParameter[]{
                    new SqlParameter("@tjnd",tjnd),
                    new SqlParameter("@districtid",districtid),
                    };
                    DataSet ds = SqlHelper.ExecuteDataset(WebConn.connString, CommandType.Text, sql, ps);
                    return ds;
                }
            }
            else
            {
                if (string.IsNullOrEmpty(districtid))
                {
                    string sql = @"
                   SELECT a.Age as Tjnd,a.Age as gradebh,a.xb,sum(a.h7) as h7,sum(a.h8) as h8,sum(a.h9) as h9,sum(a.h10) as h10,sum(a.h11) as h11,sum(a.h12) as h12,sum(a.h17b) as h17b,sum(a.h18b) as h18b
                    from Report_TB_Age a
                    where schooltype=@schooltype and tjnd=@tjnd and rid='01'  group by a.Age,a.xb order by a.Age,xb
                    ";
                    SqlParameter[] ps = new SqlParameter[]{
                    new SqlParameter("@schooltype",schooltype),
                    new SqlParameter("@tjnd",tjnd),
                    };
                    DataSet ds = SqlHelper.ExecuteDataset(WebConn.connString, CommandType.Text, sql, ps);
                    return ds;
                }
                else
                {
                    string sql = @"
                    SELECT a.Age as Tjnd,a.Age as gradebh,a.xb,sum(a.h7) as h7,sum(a.h8) as h8,sum(a.h9) as h9,sum(a.h10) as h10,sum(a.h11) as h11,sum(a.h12) as h12,sum(a.h17b) as h17b,sum(a.h18b) as h18b from Report_TB_Age a
                    where schooltype=@schooltype and tjnd=@tjnd and rid='01' and (a.DISTRICTID=@districtid or a.cityid=@districtid) group by a.Age,a.xb order by a.Age,xb
                    ";
                    SqlParameter[] ps = new SqlParameter[]{
                    new SqlParameter("@schooltype",schooltype),
                    new SqlParameter("@tjnd",tjnd),
                    new SqlParameter("@districtid",districtid),
                    };
                    DataSet ds = SqlHelper.ExecuteDataset(WebConn.connString, CommandType.Text, sql, ps);
                    return ds;
                }
            }

        }
        public DataSet GetReportXw(string tjnd, string schooltype, string districtid)
        {
            if (string.IsNullOrEmpty(schooltype))
            {
                if (string.IsNullOrEmpty(districtid))
                {
                    string sql = @"
                   SELECT a.Age as Tjnd,a.Age as gradebh,a.xb,sum(a.h1) as h1,sum(a.h2) as h2,sum(a.h3) as h3,sum(a.h4) as h4,sum(a.h5) as h5,sum(a.h6) as h6,sum(h15b) as h15b,sum(h16b) as h16b 
                    from Report_TB_Age a where tjnd=@tjnd and rid='02' group by a.Age,a.xb order by a.Age,xb
                    ";
                    SqlParameter[] ps = new SqlParameter[]{
                    new SqlParameter("@tjnd",tjnd),
                    };
                    DataSet ds = SqlHelper.ExecuteDataset(WebConn.connString, CommandType.Text, sql, ps);
                    return ds;
                }
                else
                {
                    string sql = @"
                   SELECT a.Age as Tjnd,a.Age as gradebh,a.xb,sum(a.h1) as h1,sum(a.h2) as h2,sum(a.h3) as h3,sum(a.h4) as h4,sum(a.h5) as h5,sum(a.h6) as h6,sum(h15b) as h15b,sum(h16b) as h16b 
                    from Report_TB_Age a where tjnd=@tjnd and rid='02' and (a.DISTRICTID=@districtid or a.cityid=@districtid)
                    group by a.Age,a.xb order by a.Age,xb
                    ";
                    SqlParameter[] ps = new SqlParameter[]{
                    new SqlParameter("@tjnd",tjnd),
                    new SqlParameter("@districtid",districtid),
                    };
                    DataSet ds = SqlHelper.ExecuteDataset(WebConn.connString, CommandType.Text, sql, ps);
                    return ds;
                }
            }
            else
            {
                if (string.IsNullOrEmpty(districtid))
                {
                    string sql = @"
                   SELECT a.Age as Tjnd,a.Age as gradebh,a.xb,sum(a.h1) as h1,sum(a.h2) as h2,sum(a.h3) as h3,sum(a.h4) as h4,sum(a.h5) as h5,sum(a.h6) as h6,sum(h15b) as h15b,sum(h16b) as h16b 
                    from Report_TB_Age a where schooltype=@schooltype and tjnd=@tjnd and rid='02' group by a.Age,a.xb order by a.Age,xb
                    ";
                    SqlParameter[] ps = new SqlParameter[]{
                    new SqlParameter("@schooltype",schooltype),
                    new SqlParameter("@tjnd",tjnd),
                    };
                    DataSet ds = SqlHelper.ExecuteDataset(WebConn.connString, CommandType.Text, sql, ps);
                    return ds;
                }
                else
                {
                    string sql = @"
                   SELECT a.Age as Tjnd,a.Age as gradebh,a.xb,sum(a.h1) as h1,sum(a.h2) as h2,sum(a.h3) as h3,sum(a.h4) as h4,sum(a.h5) as h5,sum(a.h6) as h6,sum(h15b) as h15b,sum(h16b) as h16b 
                    from Report_TB_Age a where schooltype=@schooltype and tjnd=@tjnd and rid='02' and (a.DISTRICTID=@districtid or a.cityid=@districtid)
                    group by a.Age,a.xb order by a.Age,xb
                    ";
                    SqlParameter[] ps = new SqlParameter[]{
                    new SqlParameter("@schooltype",schooltype),
                    new SqlParameter("@tjnd",tjnd),
                    new SqlParameter("@districtid",districtid),
                    };
                    DataSet ds = SqlHelper.ExecuteDataset(WebConn.connString, CommandType.Text, sql, ps);
                    return ds;
                }
            }
        }
        public DataSet GetReportFhl(string tjnd, string schooltype, string districtid)
        {
            if (string.IsNullOrEmpty(schooltype))
            {
                if (string.IsNullOrEmpty(districtid))
                {
                    string sql = @"
                   SELECT a.Age as Tjnd,a.Age as gradebh,a.xb,sum(a.h7) as h7,sum(a.h8) as h8,sum(a.h9) as h9,sum(a.h10) as h10,sum(a.h11) as h11,sum(a.h12) as h12,sum(h14) as h14,sum(h15) as h15,sum(a.h17b) as h17b,sum(a.h18b) as h18b from 
                   Report_TB_Age a  where tjnd=@tjnd and rid='02'  group by a.Age,a.xb order by a.Age,xb
                    ";
                    SqlParameter[] ps = new SqlParameter[]{
                    new SqlParameter("@tjnd",tjnd),
                    };
                    DataSet ds = SqlHelper.ExecuteDataset(WebConn.connString, CommandType.Text, sql, ps);
                    return ds;
                }
                else
                {
                    string sql = @"
                   SELECT a.Age as Tjnd,a.Age as gradebh,a.xb,sum(a.h7) as h7,sum(a.h8) as h8,sum(a.h9) as h9,sum(a.h10) as h10,sum(a.h11) as h11,sum(a.h12) as h12,sum(h14) as h14,sum(h15) as h15,sum(a.h17b) as h17b,sum(a.h18b) as h18b from 
                   Report_TB_Age a where tjnd=@tjnd and rid='02' and (a.DISTRICTID=@districtid or a.cityid=@districtid) group by a.Age,a.xb order by a.Age,xb
                    ";
                    SqlParameter[] ps = new SqlParameter[]{
                    new SqlParameter("@tjnd",tjnd),
                    new SqlParameter("@districtid",districtid),
                    };
                    DataSet ds = SqlHelper.ExecuteDataset(WebConn.connString, CommandType.Text, sql, ps);
                    return ds;
                }
            }
            else
            {
                if (string.IsNullOrEmpty(districtid))
                {
                    string sql = @"
                   SELECT a.Age as Tjnd,a.Age as gradebh,a.xb,sum(a.h7) as h7,sum(a.h8) as h8,sum(a.h9) as h9,sum(a.h10) as h10,sum(a.h11) as h11,sum(a.h12) as h12,sum(h14) as h14,sum(h15) as h15,sum(a.h17b) as h17b,sum(a.h18b) as h18b from 
                   Report_TB_Age a where schooltype=@schooltype and tjnd=@tjnd and rid='02'  group by a.Age,a.xb order by a.Age,xb
                    ";
                    SqlParameter[] ps = new SqlParameter[]{
                    new SqlParameter("@schooltype",schooltype),
                    new SqlParameter("@tjnd",tjnd),
                    };
                    DataSet ds = SqlHelper.ExecuteDataset(WebConn.connString, CommandType.Text, sql, ps);
                    return ds;
                }
                else
                {
                    string sql = @"
                   SELECT a.Age as Tjnd,a.Age as gradebh,a.xb,sum(a.h7) as h7,sum(a.h8) as h8,sum(a.h9) as h9,sum(a.h10) as h10,sum(a.h11) as h11,sum(a.h12) as h12,sum(h14) as h14,sum(h15) as h15,sum(a.h17b) as h17b,sum(a.h18b) as h18b from 
                   Report_TB_Age a where schooltype=@schooltype and tjnd=@tjnd and rid='02' and (a.DISTRICTID=@districtid or a.cityid=@districtid) group by a.Age,a.xb order by a.Age,xb
                    ";
                    SqlParameter[] ps = new SqlParameter[]{
                    new SqlParameter("@schooltype",schooltype),
                    new SqlParameter("@tjnd",tjnd),
                    new SqlParameter("@districtid",districtid),
                    };
                    DataSet ds = SqlHelper.ExecuteDataset(WebConn.connString, CommandType.Text, sql, ps);
                    return ds;
                }
            }
        }
    }
}
